USE [RLZY]
GO

/****** Object:  StoredProcedure [dbo].[pro_SQB_BB2]    Script Date: 08/11/2014 17:29:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





create proc [dbo].[pro_SQB_BB2](@lastdate varchar(20),@thisdate varchar(20),@bm varchar(20))
as
declare  @VarX2 int,@VarX3 int, @VarX4 int, @VarX5 int,  @VarX6 Decimal(18,1),@VarX7 int,  @VarX8 int,   @VarX9 int,  @VarX10 int,  @VarX11 int,  @VarX12 Decimal(18,1), @VarX13 Decimal(18,1),@VarX14 Decimal(18,2),  @VarX15 Decimal(18,1),@VarX16 Decimal(18,1),@VarX17 Decimal(18,2),  @VarX18  Decimal(18,2)   
select @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸') and (用工性质 like '%固%' or 用工性质 like '%制%' or 用工性质 like '%合%') and  substring(日期,1,7)=@lastdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('上月--合计',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18)  
select  @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and 用工性质 like '%固%' and  substring(日期,1,7)=@lastdate 
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('上月--固',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18)  
select @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and 用工性质 like '%制%' and  substring(日期,1,7)=@lastdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('上月--制',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18) 
select  @VarX2=Sum(人数) ,@VarX3=Sum(技能工资) ,@VarX4=Sum(岗位工资)  ,@VarX5=Sum(绩效工资),@VarX6=Sum(三项补贴),@VarX7=Sum(回贴),@VarX8=Sum(工龄津贴),@VarX9=Sum(职称津贴),@VarX10=Sum(特岗津贴),@VarX11=Sum(卫生纸费),@VarX12=Sum(中晚班费),@VarX13=Sum(加班工资),@VarX14=Sum(补发),@VarX15=Sum(扣事假工资),@VarX16=Sum(扣病假工资),@VarX17=Sum(其它扣款),@VarX18=Sum(应发总额)  from GZ where (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  and 用工性质 like '%合%' and  substring(日期,1,7)=@lastdate
insert into GZ_SQB(用工性质,[人数],  [技能工资], [岗位工资], [绩效工资], [三项补贴], [回贴], [工龄津贴], [职称津贴], [特岗津贴], [卫生纸费], [中晚班费], [加班工资], [补发], [扣事假工资],  [扣病假工资],[其它扣款],[应发总额]) values ('上月--合',@VarX2,@VarX3,@VarX4,@VarX5,@VarX6,@VarX7,@VarX8,@VarX9,@VarX10,@VarX11,@VarX12,@VarX13,@VarX14,@VarX15,@VarX16,@VarX17,@VarX18) 
if  exists (select name from sysobjects where name='GZ1')  drop table GZ1
select * into GZ1 from GZ where substring(日期,1,7)=@lastdate
if  exists (select name from sysobjects where name='GZ2') drop table GZ2
 select * into GZ2 from GZ where substring(日期,1,7)=@thisdate
 if  exists (select name from sysobjects where name='GZ3') drop table GZ3
 select   a.[部门单位] as 部门单位本月, a.[车间班组]  as 车间班组本月, a.[姓名] as 姓名本月, a.[用工性质] as 用工性质本月, a.[技能工资] as 技能工资本月, a.[岗位工资] as 岗位工资本月, a.[绩效工资] as 绩效工资本月,  a.[三项补贴] as 三项补贴本月, a.[回贴] as 回贴本月, a.[工龄津贴] as 工龄津贴本月, a.[职称津贴] as 职称津贴本月, a.[特岗津贴] as 特岗津贴本月, a.[行车工津贴] as 行车工津贴本月 , a.[卫生纸费] as 卫生纸费本月,  a.[中晚班费] as 中晚班费本月,  a.[加班工资]  as 加班工资本月, a.[补发] as 补发本月,  a.[扣事假工资] as 扣事假工资本月,  a.[扣病假工资] as 扣病假工资本月,  a.[其它扣款] as 其它扣款本月, a.[应发总额] as 应发总额本月, a.[扣失业保险] as 扣失业保险本月, a.[扣养老金] as 扣养老金本月, a.[扣医疗保险] as 扣医疗保险本月, a.[扣公积金] as 扣公积金本月, a.[扣电视费] as 扣电视费本月, a.[扣治安费] as 扣治安费本月, a.[扣会费] as 扣会费本月, a.[扣互助金] as 扣互助金本月, a.[扣丧葬费] as 扣丧葬费本月, a.[实发金额] as 实发金额本月, a.[身份证号] as 身份证号本月,a.人数  as 人数本月 ,  b.[部门单位] as 部门单位上月, b.[车间班组]  as 车间班组上月, b.[姓名] as 姓名上月, b.[用工性质] as 用工性质上月, b.[技能工资] as 技能工资上月, b.[岗位工资] as 岗位工资上月, b.[绩效工资] as 绩效工资上月,  b.[三项补贴] as 三项补贴上月, b.[回贴] as 回贴上月, b.[工龄津贴] as 工龄津贴上月, b.[职称津贴] as 职称津贴上月, b.[特岗津贴] as 特岗津贴上月, b.[行车工津贴] as 行车工津贴上月 , b.[卫生纸费] as 卫生纸费上月,  b.[中晚班费] as 中晚班费上月,  b.[加班工资]  as 加班工资上月, b.[补发] as 补发上月,  b.[扣事假工资] as 扣事假工资上月,  b.[扣病假工资] as 扣病假工资上月,  b.[其它扣款] as 其它扣款上月, b.[应发总额] as 应发总额上月, b.[扣失业保险] as 扣失业保险上月, b.[扣养老金] as 扣养老金上月, b.[扣医疗保险] as 扣医疗保险上月, b.[扣公积金] as 扣公积金上月, b.[扣电视费] as 扣电视费上月, b.[扣治安费] as 扣治安费上月, b.[扣会费] as 扣会费上月, b.[扣互助金] as 扣互助金上月, b.[扣丧葬费] as 扣丧葬费上月, b.[实发金额] as 实发金额上月 ,b.[身份证号] as 身份证号上月,b.人数  as 人数上月 into GZ3 from GZ2 as a  full join  GZ1 as b on a.身份证号= b.身份证号
 if  exists (select name from sysobjects where name='GZ4') drop table GZ4
 select    部门单位本月 as 部门单位,  车间班组本月 as 车间班组,  姓名本月 as 姓名,  用工性质本月 as 用工性质, 人数本月 as 人数, 技能工资本月 as 技能工资,  岗位工资本月  as 岗位工资,  绩效工资本月  as 绩效工资,   三项补贴本月 as 三项补贴,  回贴本月 as 回贴,  工龄津贴本月 as 工龄津贴,  职称津贴本月 as 职称津贴,  特岗津贴本月 as 特岗津贴,  行车工津贴本月 as 行车工津贴,  卫生纸费本月 as 卫生纸费,   中晚班费本月 as 中晚班费,   加班工资本月 as 加班工资,  补发本月 as 补发,   扣事假工资本月 as  扣事假工资,   扣病假工资本月 as 扣病假工资,   其它扣款本月 as 其它扣款,  应发总额本月 as 应发总额,  扣失业保险本月 as 扣失业保险,  扣养老金本月 as 扣养老金,  扣医疗保险本月 as 扣医疗保险,  扣公积金本月 as 扣公积金,  扣电视费本月 as 扣电视费,  扣治安费本月 as 扣治安费,  扣会费本月 as 扣会费,  扣互助金本月 as 扣互助金,  扣丧葬费本月 as 扣丧葬费,  实发金额本月 as 实发金额  ,身份证号本月 as 身份证号  into GZ4  from GZ3 where  身份证号上月  is null   
if  exists (select name from sysobjects where name='GZ5') drop table GZ5
select    部门单位上月 as 部门单位,  车间班组上月 as 车间班组,  姓名上月 as 姓名,  用工性质上月 as 用工性质, 人数上月 as 人数, 技能工资上月 as 技能工资,  岗位工资上月  as 岗位工资,  绩效工资上月  as 绩效工资,   三项补贴上月 as 三项补贴,  回贴上月 as 回贴,  工龄津贴上月 as 工龄津贴,  职称津贴上月 as 职称津贴,  特岗津贴上月 as 特岗津贴,  行车工津贴上月 as 行车工津贴,  卫生纸费上月 as 卫生纸费,   中晚班费上月 as 中晚班费,   加班工资上月 as 加班工资,  补发上月 as 补发,   扣事假工资上月 as  扣事假工资,   扣病假工资上月 as 扣病假工资,   其它扣款上月 as 其它扣款,  应发总额上月 as 应发总额,  扣失业保险上月 as 扣失业保险,  扣养老金上月 as 扣养老金,  扣医疗保险上月 as 扣医疗保险,  扣公积金上月 as 扣公积金,  扣电视费上月 as 扣电视费,  扣治安费上月 as 扣治安费,  扣会费上月 as 扣会费,  扣互助金上月 as 扣互助金,  扣丧葬费上月 as 扣丧葬费,  实发金额上月 as 实发金额  ,身份证号上月 as 身份证号  into GZ5 from GZ3 where  身份证号本月  is null 
if  exists (select name from sysobjects where name='GZ6') drop table GZ6
select    部门单位本月 as 部门单位,  车间班组本月 as 车间班组,  姓名本月 as 姓名,  用工性质本月 as 用工性质, 人数本月 as 人数, 技能工资本月 as 技能工资,  岗位工资本月  as 岗位工资,  绩效工资本月  as 绩效工资,   三项补贴本月 as 三项补贴,  回贴本月 as 回贴,  工龄津贴本月 as 工龄津贴,  职称津贴本月 as 职称津贴,  特岗津贴本月 as 特岗津贴,  行车工津贴本月 as 行车工津贴,  卫生纸费本月 as 卫生纸费,   中晚班费本月 as 中晚班费,   加班工资本月 as 加班工资,  补发本月 as 补发,   扣事假工资本月 as  扣事假工资,   扣病假工资本月 as 扣病假工资,   其它扣款本月 as 其它扣款,  应发总额本月 as 应发总额,  扣失业保险本月 as 扣失业保险,  扣养老金本月 as 扣养老金,  扣医疗保险本月 as 扣医疗保险,  扣公积金本月 as 扣公积金,  扣电视费本月 as 扣电视费,  扣治安费本月 as 扣治安费,  扣会费本月 as 扣会费,  扣互助金本月 as 扣互助金,  扣丧葬费本月 as 扣丧葬费,  实发金额本月 as 实发金额   into GZ6  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null)   
if  exists (select name from sysobjects where name='GZ61') drop table GZ61
select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额   into GZ61  from GZ6 where  (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  group by 用工性质 
if  exists (select name from sysobjects where name='GZ7') drop table GZ7
select    部门单位上月 as 部门单位,  车间班组上月 as 车间班组,  姓名上月 as 姓名,  用工性质上月 as 用工性质, 人数上月 as 人数, 技能工资上月 as 技能工资,  岗位工资上月  as 岗位工资,  绩效工资上月  as 绩效工资,   三项补贴上月 as 三项补贴,  回贴上月 as 回贴,  工龄津贴上月 as 工龄津贴,  职称津贴上月 as 职称津贴,  特岗津贴上月 as 特岗津贴,  行车工津贴上月 as 行车工津贴,  卫生纸费上月 as 卫生纸费,   中晚班费上月 as 中晚班费,   加班工资上月 as 加班工资,  补发上月 as 补发,   扣事假工资上月 as  扣事假工资,   扣病假工资上月 as 扣病假工资,   其它扣款上月 as 其它扣款,  应发总额上月 as 应发总额,  扣失业保险上月 as 扣失业保险,  扣养老金上月 as 扣养老金,  扣医疗保险上月 as 扣医疗保险,  扣公积金上月 as 扣公积金,  扣电视费上月 as 扣电视费,  扣治安费上月 as 扣治安费,  扣会费上月 as 扣会费,  扣互助金上月 as 扣互助金,  扣丧葬费上月 as 扣丧葬费,  实发金额上月 as 实发金额   into GZ7  from GZ3 where  not(身份证号上月  is null  or 身份证号本月  is null)  
if  exists (select name from sysobjects where name='GZ71') drop table GZ71
select    用工性质, sum(人数) as 人数, sum(技能工资) as 技能工资,  sum(岗位工资)  as 岗位工资,  sum(绩效工资)  as 绩效工资,   sum(三项补贴) as 三项补贴,  sum(回贴) as 回贴,  sum(工龄津贴) as 工龄津贴,  sum(职称津贴) as 职称津贴,  sum(特岗津贴) as 特岗津贴,  sum(行车工津贴) as 行车工津贴,  sum(卫生纸费) as 卫生纸费,   sum(中晚班费) as 中晚班费,   sum(加班工资) as 加班工资,  sum(补发) as 补发,   sum(扣事假工资) as  扣事假工资,   sum(扣病假工资) as 扣病假工资,   sum(其它扣款) as 其它扣款,  sum(应发总额) as 应发总额,  Sum(扣失业保险) as 扣失业保险,  sum(扣养老金) as 扣养老金,  sum(扣医疗保险) as 扣医疗保险,  sum(扣公积金) as 扣公积金,  sum(扣电视费) as 扣电视费,  sum(扣治安费) as 扣治安费,  sum(扣会费) as 扣会费,  sum(扣互助金) as 扣互助金,  sum(扣丧葬费) as 扣丧葬费,  Sum(实发金额) as 实发金额   into GZ71  from GZ7 where  (部门单位='特钢机关' or 部门单位='型钢厂'  or  部门单位='冷带厂' or  部门单位='新安特' or  部门单位='工贸')  group by 用工性质 
update GZ71 set 用工性质=用工性质+'---上月'   
insert into GZ61  select   用工性质,  人数,  技能工资,   岗位工资,   绩效工资,    三项补贴,   回贴,   工龄津贴,   职称津贴,   特岗津贴,   行车工津贴,   卫生纸费,    中晚班费,    加班工资,   补发,     扣事假工资,    扣病假工资,    其它扣款,   应发总额,   扣失业保险,   扣养老金,   扣医疗保险,   扣公积金,   扣电视费,   扣治安费,   扣会费,   扣互助金,   扣丧葬费,  实发金额     from GZ71   
	
	
	

	


GO

